if exists(select 1
		  from sysobjects
		  where name = 'sp_PrepareStatistic')
  drop procedure [sp_PrepareStatistic]
go


CREATE PROCEDURE [dbo].[sp_PrepareStatistic] 
AS
BEGIN
  declare @LastDate Date,
		  @DateDiff int,
		  @TmpDate Date,
		  @CandidatesWork int,
		  @CandidatesActive int,
		  @CandidateTrial int,
		  @VacanciesActive int,
		  @EmployersPerson int,
		  @Employers int
  
  select @LastDate = max([Date])
  from DayStatistics ds 
  
  set @LastDate = isnull(@LastDate, dateadd(day, -1, getdate()))
  
  if @LastDate = cast(getdate() as date)
    set @LastDate = dateadd(day, -1, getdate())
 
  set @DateDiff = datediff(day, @LastDate, getdate())
  
  select
    @CandidatesWork = sum(case
						    when CandidateTypeId = 2 then 1
						    else 0
						  end),
	@CandidatesActive = sum(case
						    when CandidateTypeId = 4 then 1
						    else 0
						  end),
	@CandidateTrial = sum(case
						    when CandidateTypeId = 3 then 1
						    else 0
						  end)
  from Candidates
  where IsCandidate = 1
  
  select
    @EmployersPerson = count(1)
  from Candidates
  where IsContactPerson = 1
  
  select 
    @Employers = count(1)
  from Employers
  
  select
    @VacanciesActive = count(1)
  from Vacancies
  where VacancyTypeId = 4
  
 
  
  while (@DateDiff > 0) begin
    set @TmpDate = dateadd(day, @DateDiff, @LastDate)
    
    merge DayStatistics as target
    using (select 
			 @TmpDate,
			 @CandidatesWork,
			 @CandidatesActive,
			 @CandidateTrial,
			 @VacanciesActive,
			 @EmployersPerson,
			 @Employers,
			 (select count(distinct seh.CandidatId)
		      from SentEmailsHistory seh
			  where cast(seh.SendDate as date) = @TmpDate and
	                seh.IsContactInfoAttached = 0),
	         (select count(distinct seh.CandidatId)
		      from SentEmailsHistory seh
			  where cast(seh.SendDate as date) = @TmpDate and
	                seh.IsContactInfoAttached = 1)
		   ) 
		   as source ([Date], CandidatesWork, CandidatesActive, CandidateTrial, VacanciesActive,
					  EmployersPerson, Employers, SendResumes, SendContacts)
    on (target.Date = source.[Date])
    WHEN MATCHED THEN 
        UPDATE SET 
          [Date] = source.[Date],
          CandidatesWork = source.CandidatesWork,
          CandidatesActive = source.CandidatesActive,
          CandidateTrial = source.CandidateTrial,
          Vacancies = source.VacanciesActive,
		  EmployersPerson = source.EmployersPerson,
		  Employers = source.Employers,
		  CV = source.SendResumes,
		  Contacts = source.SendContacts
	WHEN NOT MATCHED THEN	
	    INSERT ([Date], CandidatesWork, CandidatesActive, CandidateTrial, Vacancies,
			    EmployersPerson, Employers, CV, Contacts)
	    VALUES (source.[Date], source.CandidatesWork, source.CandidatesActive, source.CandidateTrial,
	            source.VacanciesActive, source.EmployersPerson, source.Employers, source.SendResumes, source.SendContacts );
    
    set @DateDiff = @DateDiff - 1
  end 
 END


GO


